﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using CmuCertificates.Database;
using System.Data;
using System.Windows.Forms;

namespace CmuCertificates
{
    class Business
    {   // Declare variable
        private DataAsset dataAsset = new DataAsset();

        public IWorkbook readExcelFile(string path)
        {
            return dataAsset.readExcelFile(path);
        }

        #region LinQ Query
        // Select All
        public IQueryable<Certificate> SelectAll_Certificate()
        {
            return dataAsset.SelectAll_Certificate();
        }
        public IQueryable<Class> SelectAll_Class()
        {
            return dataAsset.SelectAll_Class();
        }
        public IQueryable<Course> SelectAll_Courses()
        {
            return dataAsset.SelectAll_Courses();
        }
        public IQueryable<Lecturer> SelectAll_Lecturer()
        {
            return dataAsset.SelectAll_Lecturer();
        }
        public IQueryable<Student> SelectAll_Student()
        {
            return dataAsset.SelectAll_Student();
        }
        public IQueryable<Year> SelectAll_Year()
        {
            return dataAsset.SelectAll_Year();
        }
        // Select each item based on equal condition
        public IQueryable<Certificate> Select_Certificate(string certificateID, string studentID, string courseID, string Score)
        {
            return dataAsset.Select_Certificate(certificateID, studentID, courseID, Score);
        }
        public IQueryable<Class> Select_Class(string classID, string className, string classYear)
        {
            return dataAsset.Select_Class(classID, className, classYear);
        }
        public IQueryable<Course> Select_Courses(string courseID, string courseName, string courseShortName, string yearID, string LectureID)
        {
            return dataAsset.Select_Courses(courseID, courseName, courseShortName, yearID, LectureID);
        }
        public IQueryable<Lecturer> Select_Lecturer(string lectureID, string lectureName)
        {
            return dataAsset.Select_Lecturer(lectureID, lectureName);
        }
        public IQueryable<Student> Select_Student(string studentID, string firstName, string middleName, string lastName, string classID)
        {
            return dataAsset.Select_Student(studentID, firstName, middleName, lastName, classID);
        }
        public IQueryable<Year> Select_Year(string yearID, string year, string semester)
        {
            return dataAsset.Select_Year(yearID, year, semester);
        }
        #endregion

        #region Add to Database Function
        public void AddCertificate(Certificate certificate)
        {
            dataAsset.AddCertificate(certificate);
        }
        public void AddClass(Class classes)
        {
            dataAsset.AddClass(classes);
        }
        public void AddCourse(Course course)
        {
            dataAsset.AddCourse(course);
        }
        public void AddLecturer(Lecturer lecture)
        {
            dataAsset.AddLecturer(lecture);
        }
        public void AddStudent(Student student)
        {
            dataAsset.AddStudent(student);
        }
        public void AddYear(Year year)
        {
            dataAsset.AddYear(year);
        }
        #endregion

        #region Get Datatable
        // Get Datatable in database
        public DataTable GetCertificateDataTable_All()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Tên sinh viên");
            dt.Columns.Add("Môn");
            dt.Columns.Add("Điểm");
            dt.Columns.Add("Năm");
            dt.Columns.Add("Học kỳ");
            foreach (var item in SelectAll_Certificate())
            {
                dt.Rows.Add(new object[] {
                item.Student.LastName+" " + item.Student.MiddleName +" " +item.Student.FirstName,
                item.Course.CourseName,
                item.Score,
                item.Course.Year.Year1,
                item.Course.Year.Semester});
            }
            return dt;
        }
        public DataTable GetClassDataTable_All()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Tên lớp");
            dt.Columns.Add("Năm học");
            foreach (var item in SelectAll_Class())
            {
                dt.Rows.Add(new object[] {
                item.ClassName,
                item.ClassYear});
            }
            return dt;
        }
        public DataTable GetCourseDataTable_All()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Tên môn học");
            dt.Columns.Add("Viết tắt");
            dt.Columns.Add("Năm");
            dt.Columns.Add("Học kỳ");
            dt.Columns.Add("Tên giảng viên");
            foreach (var item in SelectAll_Courses())
            {
                dt.Rows.Add(new object[] {
                item.CourseName,
                item.CourseShortName,
                item.Year.Year1,
                item.Year.Semester,
                item.Lecturer.LecturerName});
            }
            return dt;
        }
        public DataTable GetLecturerDataTable_All()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Tên giảng viên");
            foreach(var item in SelectAll_Lecturer()) 
                dt.Rows.Add(new object[] {
                item.LecturerName});
            return dt;
        }
        public DataTable GetStudentDataTable_All()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Tên");
            dt.Columns.Add("Tên lót");
            dt.Columns.Add("Họ");
            dt.Columns.Add("Giới tính");
            dt.Columns.Add("Ngày sinh");
            dt.Columns.Add("Lớp");
            foreach (var item in SelectAll_Student())
            {
                dt.Rows.Add(new object[] {
                item.FirstName,
                item.MiddleName,
                item.LastName,
                item.Gender ? "Nam" : "Nữ",
                item.Birthday,
                item.Class.ClassName});
            }
            return dt;
        }
        public DataTable GetYearDataTable_All()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Năm");
            dt.Columns.Add("Học kỳ");
            foreach (var item in SelectAll_Year())
            {
                dt.Rows.Add(new object[] {
                item.Year1,
                item.Semester});
            }
            return dt;
        }

        // Get Datatable for DSSV
        public DataTable GetDSSVDataTable_All()
        {
            DataTable dt = new DataTable();
            DataTableDSSVColumn(dt);
            foreach (var item in SelectAll_Student())
            {
                dt.Rows.Add(new object[] 
                {item.Class.ClassName,
                item.StudentID,
                item.FirstName,
                item.MiddleName,
                item.LastName,
                item.Gender ? "Nam" : "Nữ",
                item.Birthday});
            }
            return dt;
        }
        public DataTable GetDSSVDataTable_ImportClass(string className)
        {
            DataTable dt = new DataTable();
            DataTableDSSVColumn(dt);
            foreach (var item in SelectAll_Student())
            {
                if (item.Class.ClassName == className)
                {
                    dt.Rows.Add(new object[] 
                    {item.Class.ClassName,
                    item.StudentID,
                    item.FirstName,
                    item.MiddleName,
                    item.LastName,
                    item.Gender ? "Nam" : "Nữ",
                    item.Birthday});
                }
            }
            return dt;
        }
        // Format column for DSSV
        private static void DataTableDSSVColumn(DataTable dt)
        {
            dt.Columns.Add("Class");
            dt.Columns.Add("Student ID");
            dt.Columns.Add("First Name");
            dt.Columns.Add("Middle Name");
            dt.Columns.Add("Last Name");
            dt.Columns.Add("Gender");
            dt.Columns.Add("Birthday");
        }

        // Get Datatable for DSCC
        public DataTable GetDSCCDataTable_All()
        {
            DataTable dt = new DataTable();
            DataTableDSCCColumn(dt);
            foreach (var item_chungchi in SelectAll_Certificate())
            {
                var item_monhoc = Select_Courses(item_chungchi.CourseID, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY).First();
                var item_hocsinh = Select_Student(item_chungchi.StudentID, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY).First();

                dt.Rows.Add(new object[] 
                {item_chungchi.Course.CourseName,
                item_chungchi.StudentID,
                item_hocsinh.FirstName,
                item_hocsinh.MiddleName,
                item_hocsinh.LastName,
                item_hocsinh.Gender ? "Nam" : "Nữ",
                item_hocsinh.Class.ClassName,
                item_monhoc.Year.Year1,
                item_monhoc.Year.Semester,
                item_chungchi.Score});
            }
            return dt;
        }
        public DataTable GetDSCCDataTable_ImportCertificate(string monHoc, string thoiGian, string giangVienDay) {
            DataTable dt = new DataTable();
            DataTableDSCCColumn(dt);
            var queryChungChi = from chungchi in SelectAll_Certificate()
                                where (chungchi.Course.CourseShortName == monHoc
                                && chungchi.Course.Year.Year1 + ",S:" + chungchi.Course.Year.Semester == thoiGian
                                && chungchi.Course.Lecturer.LecturerName == giangVienDay)
                                select chungchi;
            foreach (var item_chungchi in queryChungChi)
            {
                var item_monhoc = Select_Courses(item_chungchi.CourseID, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY).First();
                var item_hocsinh = Select_Student(item_chungchi.StudentID, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY, GlobalVar.KITURONG_QUERY).First();

                dt.Rows.Add(new object[] 
                {item_chungchi.Course.CourseName,
                item_chungchi.StudentID,
                item_hocsinh.FirstName,
                item_hocsinh.MiddleName,
                item_hocsinh.LastName,
                item_hocsinh.Gender ? "Nam" : "Nữ",
                item_hocsinh.Class.ClassName,
                item_monhoc.Year.Year1,
                item_monhoc.Year.Semester,
                item_chungchi.Score});
            }
            return dt;
        }
        public DataTable GetDSCCDataTable_QueryCertificate(string studentID, string courseShortName, string lop, string lecturerName, string year)
        {
            DataTable dt = new DataTable();
            DataTableDSCCColumn_Query(dt);
            var queryChungChi = from cc in SelectAll_Certificate()
                                where ( (cc.StudentID == studentID || studentID.Trim() == GlobalVar.KITURONG_QUERY)
                                && (cc.Course.CourseShortName == courseShortName || courseShortName.Trim() == GlobalVar.KITURONG_QUERY)
                                && (cc.Student.Class.ClassName == lop || lop.Trim() == GlobalVar.KITURONG_QUERY)
                                && (cc.Course.Lecturer.LecturerName == lecturerName || lecturerName.Trim() == GlobalVar.KITURONG_QUERY)
                                && (cc.Course.Year.Year1 + ",S:" + cc.Course.Year.Semester == year || year.Trim() == GlobalVar.KITURONG_QUERY))
                                select cc;
            foreach (var item_chungchi in queryChungChi)
            {
                dt.Rows.Add(new object[] 
                {item_chungchi.Course.CourseName,
                item_chungchi.StudentID,
                item_chungchi.Student.FirstName,
                item_chungchi.Student.MiddleName,
                item_chungchi.Student.LastName,
                item_chungchi.Student.Gender,
                item_chungchi.Student.Class.ClassName,
                item_chungchi.Course.Year.Year1,
                item_chungchi.Course.Year.Semester,
                item_chungchi.Score});
            }
            return dt;
        }
        public DataTable GetDSCCDataTable_StatisticCertificate(string className,bool take10,int from,int to)
        {
            DataTable dt = new DataTable();
            int max = 0;
            DataTableDSCCColumn_Query(dt);
            //============================
            dt.Columns.RemoveAt(9);
            dt.Columns.RemoveAt(8);
            dt.Columns.RemoveAt(7);
            dt.Columns.RemoveAt(0);
            //============================
            dt.Columns.Add("Số cc đã đạt");
            var queryChungChi = from cc in SelectAll_Certificate()
                                where (cc.Student.Class.ClassName == className || className == "")
                                group cc by cc.StudentID into g
                                let count = g.Count()
                                orderby count descending
                                select new { Value = g.Key, Count = count };          
            try
            {
                max = queryChungChi.First().Count;
            }
            catch { }
            if (take10)
            {
                queryChungChi = queryChungChi.Take(10);
                max = 0;
            }
            //===========================================
            foreach (var item in queryChungChi)
            {
                var queryCC = from cc in SelectAll_Certificate()
                              where cc.StudentID == item.Value
                              select cc;
                queryCC = queryCC.GroupBy(x => x.StudentID).Select(y => y.FirstOrDefault());
                foreach (var item2 in queryCC)
                {
                    if (item.Count >= max && 
                        (item.Count >= from || from == 0) &&
                        (item.Count <= to || to == 0))
                    {
                        dt.Rows.Add(new object[] 
                        {item2.StudentID,
                        item2.Student.FirstName,
                        item2.Student.MiddleName,
                        item2.Student.LastName,
                        item2.Student.Gender,
                        item2.Student.Class.ClassName,
                        item.Count});
                    }
                }
            }
            return dt;
        }
        // Format column for DSCC
        private static void DataTableDSCCColumn(DataTable dt)
        {
            dt.Columns.Add("Tên môn học");
            dt.Columns.Add("MSSV");
            dt.Columns.Add("Tên");
            dt.Columns.Add("Tên lót");
            dt.Columns.Add("Họ");
            dt.Columns.Add("Giới tính");
            dt.Columns.Add("Lớp");
            dt.Columns.Add("Năm học");
            dt.Columns.Add("Học kỳ");
            dt.Columns.Add("Điểm");
        }
        private static void DataTableDSCCColumn_Query(DataTable dt)
        {
            dt.Columns.Add("Môn học");
            dt.Columns.Add("MSSV");
            dt.Columns.Add("Tên");
            dt.Columns.Add("Tên lót");
            dt.Columns.Add("Họ");
            dt.Columns.Add("Giới tính");
            dt.Columns.Add("Lớp");
            dt.Columns.Add("Năm");
            dt.Columns.Add("Học kỳ");
            dt.Columns.Add("Điểm");
        }
        #endregion

        #region manager
        public void Edit_LectureTable(DataGridView dtgv)
        {
            string[] Name = new string[dtgv.Rows.Count-1];
            for (int i = 0; i < Name.Length; i++)
            {
                Name[i] = dtgv.Rows[i].Cells[0].Value.ToString();
            }
            dataAsset.Edit_LectureTable(Name);
        }
        public void Edit_ClassTable(DataGridView dtgv)
        {
            string[] Name = new string[dtgv.Rows.Count - 1];
            string[] Year = new string[dtgv.Rows.Count - 1];
            for (int i = 0; i < Name.Length; i++)
            {
                Name[i] = dtgv.Rows[i].Cells[0].Value.ToString();
                Year[i] = dtgv.Rows[i].Cells[1].Value.ToString();
            }
            dataAsset.Edit_ClassTable(Name,Year);
        }
        public void Edit_YearTable(DataGridView dtgv)
        {
            string[] Year = new string[dtgv.Rows.Count - 1];
            string[] Semester = new string[dtgv.Rows.Count - 1];
            for (int i = 0; i < Year.Length; i++)
            {
                Year[i] = dtgv.Rows[i].Cells[0].Value.ToString();
                Semester[i] = dtgv.Rows[i].Cells[1].Value.ToString();
            }
            dataAsset.Edit_YearTable(Year, Semester);
        }
        public void Edit_StudentTable(DataGridView dtgv)
        {
            string[] firstName = new string[dtgv.Rows.Count - 1];
            string[] middleName = new string[dtgv.Rows.Count - 1];
            string[] lastName = new string[dtgv.Rows.Count - 1];
            bool[] gender = new bool[dtgv.Rows.Count - 1];
            DateTime[] birthDay = new DateTime[dtgv.Rows.Count - 1];
            string[] className = new string[dtgv.Rows.Count - 1];
            for (int i = 0; i < firstName.Length; i++)
            {
                firstName[i] = dtgv.Rows[i].Cells[0].Value.ToString();
                middleName[i] = dtgv.Rows[i].Cells[1].Value.ToString();
                lastName[i] = dtgv.Rows[i].Cells[2].Value.ToString();
                gender[i] = dtgv.Rows[i].Cells[3].Value.ToString() == "Nam";
                birthDay[i] = DateTime.Parse(dtgv.Rows[i].Cells[4].Value.ToString());
                className[i] = dtgv.Rows[i].Cells[5].Value.ToString();
            }
            dataAsset.Edit_StudentTable(firstName, middleName, lastName, gender, birthDay, className);
        }
        public void Edit_CertificateTable(DataGridView dtgv)
        {
            string[] Name = new string[dtgv.Rows.Count - 1];
            string[] courseName = new string[dtgv.Rows.Count - 1];
            string[] Score = new string[dtgv.Rows.Count - 1];
            string[] Year = new string[dtgv.Rows.Count - 1];
            string[] Semester = new string[dtgv.Rows.Count - 1];
            for (int i = 0; i < Name.Length; i++)
            {
                Name[i] = dtgv.Rows[i].Cells[0].Value.ToString();
                courseName[i] = dtgv.Rows[i].Cells[1].Value.ToString();
                Score[i] = dtgv.Rows[i].Cells[2].Value.ToString();
                Year[i] = dtgv.Rows[i].Cells[3].Value.ToString();
                Semester[i] = dtgv.Rows[i].Cells[4].Value.ToString();
            }
            dataAsset.Edit_CertificateTable(Name, courseName, Score, Year, Semester);
        }
        public void Edit_CourseTable(DataGridView dtgv)
        {
            string[] courseName = new string[dtgv.Rows.Count - 1];
            string[] courseShortName = new string[dtgv.Rows.Count - 1];
            string[] Year = new string[dtgv.Rows.Count - 1];
            string[] Semester = new string[dtgv.Rows.Count - 1];
            string[] LecturerName = new string[dtgv.Rows.Count - 1];
            for (int i = 0; i < courseName.Length; i++)
            {
                courseName[i] = dtgv.Rows[i].Cells[0].Value.ToString();
                courseShortName[i] = dtgv.Rows[i].Cells[1].Value.ToString();
                Year[i] = dtgv.Rows[i].Cells[2].Value.ToString();
                Semester[i] = dtgv.Rows[i].Cells[3].Value.ToString();
                LecturerName[i] = dtgv.Rows[i].Cells[4].Value.ToString();
            }
            dataAsset.Edit_CourseTable(courseName, courseShortName, Year, Semester, LecturerName);
        }
        #endregion
    }
}
